package com.util;

import com.bean.purchase.purchaseBean;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import com.bean.lineItem;
import com.bean.sales.sales1Bean;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class salesUtil {
    private static Connection source = null;
    public static Connection utils(){
        if(source==null){
            try {
                Class.forName("com.mysql.jdbc.Driver");
                String url = "jdbc:mysql://localhost:3306/huagong?characterEncoding=UTF-8";
                String user = "root";
                String password = "123456";
                source = DriverManager.getConnection(url,user,password);
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return source;
    }
    /**
     * 添加退货数据，要先查询订单管理中的全部已出库订单
     */
    public static List<lineItem> selectOrder(String userId,String status) throws SQLException {
        QueryRunner qr = new QueryRunner();
        String sql="";
        ArrayList arr=new ArrayList();
        if("0".equals(status)){
             sql = "select a.did orderId,b.name name,a.amount amount,a.price price,(a.amount * a.price) totalPrice,a.cost_price cost_price,a.other_cost otherCost,(a.amount * a.price + other_cost) totalCostPrice from `order` a,commodity b where a.commodity_id=b.sid and a.status = '1'";
        }if(!"0".equals(status)){
            sql = "select a.did orderId,b.name name,a.amount amount,a.price price,(a.amount * a.price) totalPrice,a.cost_price cost_price,a.other_cost otherCost,(a.amount * a.price + other_cost) totalCostPrice from `order` a,commodity b where a.commodity_id=b.sid and a.status = '1' ";
            if(!"".equals(userId)){
                sql+= " and a.user_id = ?";
                arr.add(userId);
            }
        }

        List<lineItem> list = qr.query(salesUtil.utils(),sql,new BeanListHandler<lineItem>(lineItem.class),arr.toArray());
        for(lineItem lineitem:list){
            String string = String.format("CG%05d",lineitem.getOrderId());
            lineitem.setOrderId1(string);
        }
        return list;
    }

    /**
     * 查询订单详情
     */
    public static lineItem selectParticulars(String orderId) throws SQLException {
        QueryRunner qr = new QueryRunner();
        String sql = "SELECT b.name name,a.amount amount,a.price price,(a.amount * a.price) totalPrice,a.cost_price cost_price,a.other_cost otherCost,(a.amount * a.cost_price + a.other_cost) totalCostPrice,a.royalties royalties,a.user_name userName,a.address address,a.phone phone,a.bill_info billInfo,a.invoice_id invoiceId,a.remarks remarks from `order` a,commodity b where a.commodity_id=b.sid and a.did=?";
        lineItem lineitem = qr.query(salesUtil.utils(),sql,new BeanHandler<lineItem>(lineItem.class),orderId);
        return lineitem;
    }
    /**
     * 添加退货
     */
    public static void insert(Object...objects) throws SQLException {
        QueryRunner qr = new QueryRunner();
        String sql = "insert into sales(user_id,order_id,tracking_number,tracking_name,status,create_time) VALUES(?,?,?,?,?,?)";
        qr.update(purchaseUtil.utils(),sql,objects);

    }
    /**
     * 修改订单表中订单状态
     */
    public static void update1(String id) throws SQLException {
        QueryRunner qr = new QueryRunner();
        String sql1 = "UPDATE `order` set status ='2' where did=?";
        qr.update(salesUtil.utils(),sql1,id);
    }
    /**
     * 查询全部退货订单
     */
    public static List<sales1Bean> selectAll() throws SQLException {
        QueryRunner qr = new QueryRunner();
        String sql = "SELECT a.did salesId,a.user_id user_id,c.did orderId,b.name,c.amount,a.tracking_name,a.tracking_number,a.create_time,a.status status  from sales a,commodity b,`order` c where a.order_id=c.did and b.sid=c.commodity_id limit 0,10";
        List<sales1Bean> list = qr.query(purchaseUtil.utils(),sql,new BeanListHandler<sales1Bean>(sales1Bean.class));
        return list;
    }
    /**
     * 获取分页页数
     */
    public static sales1Bean selectLine(String tracking_number, String name) throws SQLException {
        QueryRunner qr = new QueryRunner();
        String sql = "select count(*) line from sales a,commodity b,`order` c where a.order_id=c.did and b.sid=c.commodity_id ";
        ArrayList arr = new ArrayList();
        if(!"".equals(tracking_number) && tracking_number!=null){
            sql+=" and tracking_number=?";
            arr.add(tracking_number);
        }if(!"".equals(name) && name!=null){
            sql+=" and name like ?";
            arr.add("%"+name+"%");
        }
        sales1Bean sales = qr.query(salesUtil.utils(),sql,new BeanHandler<sales1Bean>(sales1Bean.class),arr.toArray());
        return sales;
    }
    /**
     * 根据当前页数查询数据
     */
    public static List<sales1Bean> selectPage(String tracking_number, String name, Integer page) throws SQLException {
        QueryRunner qr  = new QueryRunner();
        String sql = "SELECT a.did salesId,a.user_id user_id,c.did orderId,b.name,c.amount,a.tracking_name,a.tracking_number,a.create_time,a.status status  from sales a,commodity b,`order` c where a.order_id=c.did and b.sid=c.commodity_id and 1=1 ";
        ArrayList arr = new ArrayList();
        if(!"".equals(tracking_number) && tracking_number!=null){
            sql+=" and tracking_number=?";
            arr.add(tracking_number);
        }if(!"".equals(name) && name!=null){
            sql+=" and name like ?";
            arr.add("%"+name+"%");
        }
        sql+=" limit ?,10";
        arr.add(page);
        List<sales1Bean> list = qr.query(salesUtil.utils(),sql,new BeanListHandler<sales1Bean>(sales1Bean.class),arr.toArray());
        return list;
    }
    /**
     * 修改退货状态
     */
    public static void update(String id) throws SQLException {
        QueryRunner qr = new QueryRunner();
        String sql = "UPDATE sales set status = 1 where did=?";
        qr.update(salesUtil.utils(),sql,id);
    }
    /**
     * 查询库存id
     */
    public static sales1Bean select2(String uid) throws SQLException {
        QueryRunner qr = new QueryRunner();
        String sql = "SELECT c.number from sales a,`order` b,inventory c where a.order_id = b.did and b.invoice_id=c.number and b.did=?";
        sales1Bean sales = qr.query(salesUtil.utils(),sql,new BeanHandler<sales1Bean>(sales1Bean.class),uid);
        return sales;
    }
    /**
     * 订单退货后增加库存
     */
    public static void update2(String amount,String number) throws SQLException {
        QueryRunner qr = new QueryRunner();
        String sql = "UPDATE inventory set amount= amount+? where number=?";
        qr.update(salesUtil.utils(),sql,amount,number);
    }
    /**
     * 根据条件查询退货订单
     */
    public static List<sales1Bean> select(String tracking_number, String name) throws SQLException {
        QueryRunner qr = new QueryRunner();
        String sql = "SELECT a.did salesId,a.user_id,c.did orderId,b.name,c.amount,a.tracking_name,a.tracking_number,a.create_time,a.status status   from sales a,commodity b,`order` c where a.order_id=c.did and b.sid=c.commodity_id ";
        ArrayList arr = new ArrayList();
        if(!"".equals(tracking_number) && tracking_number!=null){
            sql+=" and tracking_number=?";
            arr.add(tracking_number);
        }if(!"".equals(name) && name!=null){
            sql+=" and name like ?";
            arr.add("%"+name+"%");
        }
        sql+=" limit 0,10";

        List<sales1Bean> list = qr.query(salesUtil.utils(),sql,new BeanListHandler<sales1Bean>(sales1Bean.class),arr.toArray());
        return list;
   }
    /**
     * 修改退货信息
     */
    public static void update(String number,String name,String id) throws SQLException {
        QueryRunner qr = new QueryRunner();
        String sql = "UPDATE sales set tracking_number=?,tracking_name=? where did=?";
        qr.update(salesUtil.utils(),sql,number,name,id);
    }
    /**
     * 查询退货信息
     */
    public static sales1Bean select1(String id) throws SQLException {
        QueryRunner qr = new QueryRunner();
        String sql = "select tracking_name,tracking_number from sales where did=?";
        sales1Bean sales1bean = qr.query(salesUtil.utils(),sql,new BeanHandler<sales1Bean>(sales1Bean.class),id);
        return sales1bean;
    }
    /**
     * 删除退货订单
     */
    public static void delete(String id) throws SQLException {
        QueryRunner qr = new QueryRunner();
        String sql = "delete from sales where did=?";
        qr.update(salesUtil.utils(),sql,id);
    }
}
